﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for ManagementService
/// </summary>
public class ManagementService
{
    SqlConnection coon;
	public ManagementService()
	{
        coon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

	}
    /*********** SERVICE ***********/

    public DataSet GetService()
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        DataSet dt = new DataSet();
        string sql = "select * from ServiceCategory order by ServiceCategoryID DESC";
        SqlDataAdapter sda = new SqlDataAdapter(sql, coon);
        sda.Fill(dt);
        return dt;
    }

    public bool DeleteService(int id)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string sql1 = "select * from SubCategory where ServiceCategoryID = " + id + "";
        SqlCommand cmd1 = new SqlCommand(sql1, coon);
        SqlDataReader sda = cmd1.ExecuteReader();
        if (sda.HasRows)
        {
            sda.Close();
            return false;
        }
        sda.Close();


        string sql2 = "select * from Contract where ServiceCategoryID = " + id + "";
        SqlCommand cmd2 = new SqlCommand(sql2, coon);
        SqlDataReader sda1 = cmd1.ExecuteReader();
        if (sda1.HasRows)
        {
            sda1.Close();
            return false;
        }
        sda1.Close();


        string sql = "DELETE FROM  ServiceCategory where ServiceCategoryID=" + id + "";
        SqlCommand cmd = new SqlCommand(sql, coon);
        int i = cmd.ExecuteNonQuery();

        coon.Close();
        return i > 0 ? true : false;
    }

    public DataSet SearchService(string s)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string sql = "select * from ServiceCategory where ServiceName like '%" + s + "%' order by ServiceCategoryID DESC";
        DataSet dt = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter(sql, coon);
        sda.Fill(dt);

        return dt;
    }


    public bool UpdateService(int id, string name, double SecurityDeposit, string img, string Description)
    {
        if (coon.State == ConnectionState.Closed)
        { coon.Open(); }

        string sql = "update ServiceCategory set ServiceName='" + name + "',SecurityDeposit='" + SecurityDeposit + "',UrlImage='" + img + "',[Description]='" + Description + "' where ServiceCategoryID = " + id + " ";
        SqlCommand cmd = new SqlCommand(sql, coon);

        int i = cmd.ExecuteNonQuery();
        coon.Close();
        return i > 0 ? true : false;
    }

    

    public List<int> GetServiceID()
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        List<int> l = new List<int>();

        string sql = "select ServiceCategoryID FROM ServiceCategory";
        SqlCommand cmd = new SqlCommand(sql, coon);
        SqlDataReader sdr = cmd.ExecuteReader();
        while (sdr.Read())
        {
            l.Add(sdr.GetInt32(0));
        }
        sdr.Close();
        coon.Close();
        return l;
    }

    public bool AddService(string name, double SecurityDeposit, string UrlImage, string Description)
    {

        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string sql = "insert into ServiceCategory values(@n,@s,@u,@d)";
        SqlCommand cmd = new SqlCommand(sql, coon);
        cmd.Parameters.AddWithValue("@n", name);
        cmd.Parameters.AddWithValue("@s", SecurityDeposit);
        cmd.Parameters.AddWithValue("@u", UrlImage);
        cmd.Parameters.AddWithValue("@d", Description);
        int i = cmd.ExecuteNonQuery();
        coon.Close();
        return i > 0 ? true : false;
    }
    public ORANGE_PROJECTModel.ServiceCategory GetEditServiceCategory(int id)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        DataSet dt = new DataSet();
        string sql = "select * from ServiceCategory where ServiceCategoryID=" + id + " ";
        SqlCommand cmd = new SqlCommand(sql, coon);
        SqlDataReader sda = cmd.ExecuteReader();
        ORANGE_PROJECTModel.ServiceCategory s = new ORANGE_PROJECTModel.ServiceCategory();
        while (sda.Read())
        {
            s.ServiceCategoryID = sda.GetInt32(0);
            s.ServiceName = sda.GetString(1);
            s.SecurityDeposit = sda.GetDouble(2);
            s.UrlImage = sda.GetString(3);
            s.Description = sda.GetString(4);
        }
        sda.Close();
        return s;
    }

    public DataSet LoadPageContract(int paymentMethodID)
    {
        string query = "SELECT ServiceCategory.ServiceCategoryID,SubCategory.SubCategoryID FROM ServiceCategory " +
                        "JOIN SubCategory " +
                        "ON ServiceCategory.ServiceCategoryID = SubCategory.ServiceCategoryID " +
                        "JOIN PaymentMethod " +
                        "ON SubCategory.SubCategoryID = PaymentMethod.SubCategoryID " +
                        "WHERE PaymentMethod.PaymentMethodID = " + paymentMethodID;
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(query, coon);
        da.Fill(ds);
        return ds;
    }
}